#include "DatabaseManager.h"

QList<User> initUser = {
    User("admin","000000",65535,Role::ADMIN,"初始化——完整权限人员管理用户"),
    User("part","000000",65535,Role::USER_INFO,"初始化——完整权限配件管理用户"),
    User("storeIO","000000",65535,Role::USER_STORE_IO,"初始化——完整权限仓库管理用户"),
    User("storeCheck","000000",65535,Role::USER_STORE_CHECK,"初始化——完整权限仓库盘点用户"),
};

DatabaseManager* DatabaseManager::m_instance = nullptr;

DatabaseManager* DatabaseManager::getInstance()
{
    if(!m_instance)
    {
        m_instance = new DatabaseManager;
    }
    return m_instance;
}

DatabaseManager::DatabaseManager() {}

/*=====================================<数据层数据库初始化>=====================================*/
//数据库初始化，并且检查程序必要的表是否存在，不存在就进行创建
void DatabaseManager::initDatabase()
{
    if(!m_isFristInit)
        return;
    m_isFristInit = false;

    if(QSqlDatabase::contains(DBLinkageName))
    {
        m_DB = QSqlDatabase::database(DBLinkageName);
#if DataBaseManager_DEBUG
        qDebug() << "{正常}已有连接:" << DBLinkageName;
#endif
    }
    else
    {
        m_DB = QSqlDatabase::addDatabase("QMYSQL", DBLinkageName);
        if(!m_DB.isValid())
            throw std::runtime_error("{异常}数据库驱动加载失败，一般是环境问题");
        else
            qDebug() << "{正常}数据驱动加载成功";
        m_DB.setHostName(DBHost);
        m_DB.setDatabaseName(DBName);
        m_DB.setPort(DBPort);
        m_DB.setUserName(CDBUserName);
        m_DB.setPassword(CDBUserPassword);

#if DataBaseManager_DEBUG
        qDebug() << "{正常}数据库无" << DBLinkageName << "连接，已创建";
#endif
    }

    if(!m_DB.open())
    {
#if DataBaseManager_DEBUG
        qDebug() << DBName << "{异常}数据库打开失败" << m_DB.lastError().text();
#endif
        throw std::runtime_error("{异常}[DB]库打开失败" + m_DB.lastError().text().toStdString());
    }

    m_query = QSqlQuery(m_DB);

    /*+++++++++++++++++<检查数据库表是否存在查询>+++++++++++++++++*/
    //用户信息表
    m_query.exec("SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'CAIDatabase' AND TABLE_NAME = 'user';");
    QUERY_IFERROR

    if (!m_query.next())
    {
        m_query.exec(
            "CREATE TABLE `user` ("
            "id bigint NOT NULL AUTO_INCREMENT COMMENT '用户id',"
            "name TEXT NOT NULL COMMENT '用户姓名',"
            "password TEXT NOT NULL COMMENT '用户密码',"
            "permissionBits SMALLINT UNSIGNED NOT NULL COMMENT '用户权限位',"
            "role ENUM('ADMIN','USER_INFO','USER_STORE_IO','USER_STORE_CHECK') NOT NULL COMMENT '用户角色',"
            "roleDescription TEXT COMMENT '用户角色描述',"
            "PRIMARY KEY (id)"
            ") ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;"
            );
        if(m_query.lastError().isValid())
        {
#if DataBaseManager_DEBUG
            qDebug()<<"{异常}创建user表执行语句失败:" << m_query.lastError().text();
#endif
            throw std::runtime_error("{异常}[DB]数据库创建user表执行语句失败" + m_query.lastError().text().toStdString());
        }

        try{
            for(auto userInfo : initUser)
                DatabaseManager::userManage_insertUserInfoRecord(userInfo);
        }catch(const std::exception& e)
        {
            throw std::runtime_error(QString("{异常}[DB]初始化user表时候的创建初始化用户失败: %1").arg(e.what()).toStdString());;
        }
    }

    //日志记录表
    m_query.exec("SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'CAIDatabase' AND TABLE_NAME = 'logEntry';");
    QUERY_IFERROR

    if (!m_query.next()) {
        m_query.exec(
            "CREATE TABLE `logEntry` ("
            "`logId` bigint NOT NULL AUTO_INCREMENT COMMENT '日志id',"
            "`operationTime` bigint DEFAULT NULL COMMENT '操作时间',"
            "`operatorName` varchar(255) DEFAULT NULL COMMENT '操作人姓名',"
            "`operatorRole` smallint unsigned DEFAULT NULL COMMENT '操作人角色',"
            "`operationDescribe` text COMMENT '操作描述',"
            "PRIMARY KEY (`logId`)"
            ") ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;"
            );
        if(m_query.lastError().isValid())
        {
#if DataBaseManager_DEBUG
            qDebug()<<"{异常}创建logEntry表执行语句失败:" << m_query.lastError().text();
#endif
            throw std::runtime_error("{异常}[DB]数据库创建logEntry表执行语句失败" + m_query.lastError().text().toStdString());
        }
    }

    //配件信息表
    m_query.exec("SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'CAIDatabase' AND TABLE_NAME = 'part';");
    QUERY_IFERROR

    if (!m_query.next()) {
        m_query.exec(
            "CREATE TABLE `part` ("
            "`id` bigint NOT NULL AUTO_INCREMENT COMMENT 'id',"
            "`name` text CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '配件名称',"
            "`model` text CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '配件型号',"
            "`price` double NOT NULL COMMENT '配件价格',"
            "`location` text CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci COMMENT '配件存储位置',"
            "`stockQuantity` int NOT NULL COMMENT '库存数量',"
            "`stockUpperLimit` int NOT NULL COMMENT '库存上限',"
            "`stockLowerLimit` int NOT NULL COMMENT '库存下限',"
            "PRIMARY KEY (`id`)"
            ") ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;"
            );
        if(m_query.lastError().isValid())
        {
#if DataBaseManager_DEBUG
            qDebug()<<"{异常}创建part表执行语句失败:" << m_query.lastError().text();
#endif
            throw std::runtime_error("{异常}[DB]数据库创建part表执行语句失败" + m_query.lastError().text().toStdString());
        }
    }

    //仓库异常记录表
    m_query.exec("SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'CAIDatabase' AND TABLE_NAME = 'storeAnomaly';");
    QUERY_IFERROR

    if (!m_query.next()) {
        m_query.exec(
            "CREATE TABLE `storeAnomaly` ("
            "`anomalyId` bigint NOT NULL AUTO_INCREMENT COMMENT '异常编号',"
            "`partId` bigint NOT NULL COMMENT '配件编号',"
            "`location` varchar(255) DEFAULT NULL COMMENT '配件位置',"
            "`actualQuantity` int DEFAULT NULL COMMENT '实际数量',"
            "`systemQuantity` int DEFAULT NULL COMMENT '系统数量',"
            "`difference` varchar(255) DEFAULT NULL COMMENT '差异描述',"
            "`confirmStatus` varchar(255) DEFAULT NULL COMMENT '确认状态',"
            "PRIMARY KEY (`anomalyId`)"
            ") ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;"
            );
        if(m_query.lastError().isValid())
        {
#if DataBaseManager_DEBUG
            qDebug()<<"{异常}创建storeAnomaly表执行语句失败:" << m_query.lastError().text();
#endif
            throw std::runtime_error("{异常}[DB]数据库创建storeAnomaly表执行语句失败" + m_query.lastError().text().toStdString());
        }
    }

    //仓库入库记录表
    m_query.exec("SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'CAIDatabase' AND TABLE_NAME = 'storeInRecord';");
    QUERY_IFERROR

    if (!m_query.next()) {
        m_query.exec(
            "CREATE TABLE `storeInRecord` ("
            "`recordId` bigint NOT NULL AUTO_INCREMENT COMMENT '仓库记录',"
            "`partId` bigint NOT NULL COMMENT '配件编号',"
            "`operationTime` bigint COMMENT '操作时间',"
            "`status` text COMMENT '操作时间',"
            "`inQuantity` int DEFAULT NULL COMMENT '入库数量',"
            "PRIMARY KEY (`recordId`)"
            ") ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;"
            );
        if(m_query.lastError().isValid())
        {
#if DataBaseManager_DEBUG
            qDebug()<<"{异常}创建storeInRecord表执行语句失败:" << m_query.lastError().text();
#endif
            throw std::runtime_error("{异常}[DB]数据库创建storeInRecord表执行语句失败" + m_query.lastError().text().toStdString());
        }
    }

    //仓库出库记录表
    m_query.exec("SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'CAIDatabase' AND TABLE_NAME = 'storeOutRecord';");
    if (!m_query.next()) {
        m_query.exec(
            "CREATE TABLE `storeOutRecord` ("
            "`recordId` bigint NOT NULL AUTO_INCREMENT COMMENT '仓库记录',"
            "`partId` bigint NOT NULL COMMENT '配件编号',"
            "`operationTime` bigint COMMENT '操作时间',"
            "`status` text COMMENT '操作时间',"
            "`outQuantity` int DEFAULT NULL COMMENT '出库数量',"
            "PRIMARY KEY (`recordId`)"
            ") ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;"
            );
        if(m_query.lastError().isValid())
        {
#if DataBaseManager_DEBUG
            qDebug()<<"{异常}创建storeOutRecord表执行语句失败:" << m_query.lastError().text();
#endif
            throw std::runtime_error("{异常}[DB]数据库创建storeOutRecord表执行语句失败" + m_query.lastError().text().toStdString());
        }
    }

    qDebug() << "{正常}初始化数据库函数结束";
}

/*=====================================<登录获取用户信息>=====================================*/
User DatabaseManager::login_getUserInfo(QString name, QString password)
{
    m_query.prepare("SELECT * FROM user WHERE name=? AND password = ?");
    m_query.addBindValue(name);
    m_query.addBindValue(password);

    if(!m_query.exec())
    {
        throw std::invalid_argument("{异常}[DB]查询指定用户名称和密码语句执行失败");
    }

    if(!m_query.next())
    {
        throw std::invalid_argument("{正常}[LOGIN]用户名或者密码错误");
    }

    User tmpLoginUserInfo(
        m_query.value("id").toInt(),
        m_query.value("name").toString(),
        m_query.value("password").toString(),
        m_query.value("permissionBits").toUInt(),
        m_query.value("roleDescription").toString());
    tmpLoginUserInfo.setRole(static_cast<Role>( roleMap[m_query.value("role").toString()] ));
    return tmpLoginUserInfo;
}


/*=======================================<用户管理相关的sql处理函数>=======================================*/
qint64 DatabaseManager::userManage_insertUserInfoRecord(const User &userInfo)
{
    if(!m_DB.isOpen())
        throw std::runtime_error("{异常}[DB]插入用户信息记录之前数据库没有被正确的打开，这一般是代码的问题");

    m_query.prepare("INSERT user"
                    "(name,password,permissionBits,role,roleDescription)"
                    "VALUES(?,?,?,?,?)");
    m_query.addBindValue(userInfo.getName());
    m_query.addBindValue(userInfo.getPassword());
    m_query.addBindValue(userInfo.getPermissionBits());
    m_query.addBindValue(static_cast<int>(userInfo.getRole()));
    m_query.addBindValue(userInfo.getRoleDescription());

    if(!m_query.exec())
    {
        throw std::runtime_error("{异常}[DB]添加用户语句执行失败: " + m_query.lastError().text().toStdString());
    }

    return m_query.lastInsertId().toInt();
}
//返回受影响的行数
qint64 DatabaseManager::userManage_deleteUserInfoRecord(qint64 userId)
{
    if(!m_DB.isOpen())
        throw std::runtime_error("{异常}[DB]删除用户信息记录之前数据库没有被正确的打开，这一般是代码的问题");

    m_query.prepare("DELETE FROM user WHERE id=?");
    m_query.addBindValue(userId);

    if(!m_query.exec())
    {
        throw std::runtime_error("{异常}[DB]删除用户语句执行失败: "+ m_query.lastError().text().toStdString());
    }
    return m_query.numRowsAffected();
}

qint64 DatabaseManager::userManage_updateUserInfoRecord(const User &newUserInfo, qint64 selectUserId)
{
    if(!m_DB.isOpen())
        throw std::runtime_error("{异常}[DB]修改用户信息记录之前数据库没有被正确的打开，这一般是代码的问题");

    m_query.prepare("UPDATE user SET name=?,password=?,permissionBits=?,role=?,roleDescription=? WHERE id=?");
    m_query.addBindValue(newUserInfo.getName());
    m_query.addBindValue(newUserInfo.getPassword());
    m_query.addBindValue(newUserInfo.getPermissionBits());
    m_query.addBindValue(static_cast<int>(newUserInfo.getRole()));
    m_query.addBindValue(newUserInfo.getRoleDescription());

    m_query.addBindValue(selectUserId);

    if(!m_query.exec())
    {
        throw std::runtime_error("{异常}[DB]修改用户语句执行失败: " + m_query.lastError().text().toStdString());
    }
    return m_query.numRowsAffected();
}

QSqlQuery DatabaseManager::userManage_findUserInfoRecord(QString name)
{
    if(!m_DB.isOpen())
        throw std::runtime_error("{异常}[DB]查询符合条件用户记录之前数据库没有被正确的打开，这一般是代码的问题");

    QSqlQuery tmpQ(m_DB);
    tmpQ.prepare("SELECT * FROM user WHERE name LIKE ?");
    tmpQ.addBindValue("%" + name + "%");

    if(!tmpQ.exec())
    {
        throw std::runtime_error("{异常}[DB]查询符合" + name.toStdString() + "条件啊姓名的用户信息记录语句执行失败:" + tmpQ.lastError().text().toStdString());
    }
    return tmpQ;
}

/*=======================================<日志管理相关的sql处理函数>=======================================*/
qint64 DatabaseManager::logManage_insertLogRecord(const LogEntry& logInfo)
{
    if(!m_DB.isOpen())
        throw std::runtime_error("{异常}[DB]插入日志记录之前数据库没有被正确的打开，这一般是代码的问题");

    m_query.prepare("INSERT logEntry(operationTime,operatorName,operatorRole,operationDescribe) "
                 "VALUES(?,?,?,?)");
    m_query.addBindValue(logInfo.getOperationTime().toMSecsSinceEpoch());
    m_query.addBindValue(logInfo.getOperatorName());
    m_query.addBindValue(static_cast<int>(logInfo.getOperatorRole()));
    m_query.addBindValue(logInfo.getOperationDescribe());
    if(!m_query.exec())
    {
        throw std::runtime_error("{异常}[DB]添加日志记录语句执行失败");
    }

    return m_query.lastInsertId().toLongLong();
}

/*===========================<配件信息记录管理相关的sql处理函数>===========================*/
qint64 DatabaseManager::partManage_insertPartInfoRecord(const Part& partInfo)
{
    if(!m_DB.isOpen())
        throw std::runtime_error("{异常}[DB]插入配件信息记录之前数据库没有被正确的打开，这一般是代码的问题");

    m_query.prepare("INSERT part"
              "(name,model,price,location,stockQuantity,stockUpperLimit,stockLowerLimit) "
              "VALUES"
              "(:name,:model,:price,:location,:stockQuantity,:stockUpperLimit,:stockLowerLimit)");

    m_query.bindValue(":name",partInfo.getName());
    m_query.bindValue(":model",partInfo.getModel());
    m_query.bindValue(":price",partInfo.getPrice());
    m_query.bindValue(":location",partInfo.getLocation());
    m_query.bindValue(":stockQuantity",partInfo.getStockQuantity());
    m_query.bindValue(":stockUpperLimit",partInfo.getStockUpperLimit());
    m_query.bindValue(":stockLowerLimit",partInfo.getStockLowerLimit());

    if(!m_query.exec())
    {
        throw std::runtime_error("添加配件语句执行失败: " + m_query.lastError().text().toStdString());
    }
    return m_query.lastInsertId().toLongLong();
}

qint64 DatabaseManager::partManage_deletePartInfoRecord(qint64 partId)
{
    if(!m_DB.isOpen())
        throw std::runtime_error("{异常}[DB]删除配件信息记录之前数据库没有被正确的打开，这一般是代码的问题");

    m_query.prepare("DELETE FROM part WHERE id = ?");
    m_query.addBindValue(partId);

    if(!m_query.exec())
    {
        throw std::runtime_error("{异常}[DB]删除配件语句执行失败: " + m_query.lastError().text().toStdString());
    }
    return m_query.numRowsAffected();
}

qint64 DatabaseManager::partManage_updatePartInfoRecord(const Part &newPartInfo, qint64 selectPartId)
{
    if(!m_DB.isOpen())
        throw std::runtime_error("{异常}[DB]修改配件信息记录之前数据库没有被正确的打开，这一般是代码的问题");

    m_query.prepare("UPDATE part SET name=?,model=?,price=?,location=?,stockQuantity=?,stockUpperLimit=?,stockLowerLimit=? WHERE id=?");
    m_query.addBindValue(newPartInfo.getName());
    m_query.addBindValue(newPartInfo.getModel());
    m_query.addBindValue(newPartInfo.getPrice());
    m_query.addBindValue(newPartInfo.getLocation());
    m_query.addBindValue(newPartInfo.getStockQuantity());
    m_query.addBindValue(newPartInfo.getStockUpperLimit());
    m_query.addBindValue(newPartInfo.getStockLowerLimit());

    m_query.addBindValue(selectPartId);

    if(!m_query.exec())
    {
        throw std::runtime_error("{异常}[DB]修改配件信息记录语句执行失败: " + m_query.lastError().text().toStdString());
    }

    return m_query.numRowsAffected();
}

QSqlQuery DatabaseManager::partManage_findPartInfoRecord(QString name)
{
    if(!m_DB.isOpen())
        throw std::runtime_error("{异常}[DB]查询符合条件配件记录之前数据库没有被正确的打开，这一般是代码的问题");

    QSqlQuery tmpQ(m_DB);
    tmpQ.prepare("SELECT * FROM part WHERE name LIKE ?");
    tmpQ.addBindValue("%" + name + "%");

    if(!tmpQ.exec())
    {
        throw std::runtime_error("{异常}[DB]查询符合" + name.toStdString() + "条件的配件信息记录语句执行失败:" + tmpQ.lastError().text().toStdString());
    }
    return tmpQ;
}
/*===========================<分配配件位置相关的sql处理函数>===========================*/
qint64 DatabaseManager::allotManage_updateLocationInfoRecord(QString newLocation, qint64 partId)
{
    if(!m_DB.isOpen())
        throw std::runtime_error("{异常}[DB]分配配件信息记录之前数据库没有被正确打开，这一般是代码问题");

    m_query.prepare("UPDATE part SET location = ? WHERE id = ?");
    m_query.addBindValue(newLocation);
    m_query.addBindValue(partId);

    if(!m_query.exec())
    {
        throw std::runtime_error("{异常}[DB]分配配件信息记录语句执行失败" + m_query.lastError().text().toStdString());
    }

    return m_query.numRowsAffected();
}

QSqlQuery DatabaseManager::allotManage_findPartInfoRecord(QString location)
{
    if(!m_DB.isOpen())
        throw std::runtime_error("{异常}[DB]查询位置条件配件记录之前数据库没有被正确的打开，这一般是代码的问题");

    QSqlQuery tmpQ(m_DB);
    tmpQ.prepare("SELECT * FROM part WHERE location LIKE ?");
    tmpQ.addBindValue("%" + location + "%");

    if(!tmpQ.exec())
    {
        throw std::runtime_error("{异常}[DB]查询符合" + location.toStdString() + "条件的配件信息记录语句执行失败:" + tmpQ.lastError().text().toStdString());
    }
    return tmpQ;
}
/*===========================<仓库出入库记录相关的sql处理函数>===========================*/
qint64 DatabaseManager::IOManage_updateQuantityinfoRecord(qint64 newNum, qint64 partId)
{
    if(!m_DB.isOpen())
        throw std::runtime_error("{异常}[DB]更新配件库存信息记录之前数据库没有被正确打开，这一般是代码问题");

    m_query.prepare("UPDATE part SET stockQuantity = ? WHERE id = ?");
    m_query.addBindValue(newNum);
    m_query.addBindValue(partId);

    if(!m_query.exec())
    {
        throw std::runtime_error("{异常}[DB]更新配件库存信息记语句执行失败" + m_query.lastError().text().toStdString());
    }

    return m_query.numRowsAffected();
}

qint64 DatabaseManager::RecordManage_insertInRecordInfoRecord(const StoreInRecord &record)
{
    if(!m_DB.isOpen())
        throw std::runtime_error("{异常}[DB]插入入库记录之前数据库没有被正确的打开，这一般是代码的问题");

    m_query.prepare("INSERT storeInRecord(partId,operationTime,status,inQuantity) "
                    "VALUES(?,?,?,?)");
    m_query.addBindValue(record.getPartId());
    m_query.addBindValue(record.getOperationTime().toMSecsSinceEpoch());
    m_query.addBindValue(record.getStatus());
    m_query.addBindValue(record.getInQuantity());
    if(!m_query.exec())
    {
        throw std::runtime_error("{异常}[DB]插入入库记录语句执行失败" + m_query.lastError().text().toStdString());
    }

    return m_query.lastInsertId().toLongLong();
}

qint64 DatabaseManager::RecordManage_insertOutRecordInfoRecord(const StoreOutRecord &record)
{
    if(!m_DB.isOpen())
        throw std::runtime_error("{异常}[DB]插入出库记录之前数据库没有被正确的打开，这一般是代码的问题");

    m_query.prepare("INSERT storeOutRecord(partId,operationTime,status,outQuantity) "
                    "VALUES(?,?,?,?)");
    m_query.addBindValue(record.getPartId());
    m_query.addBindValue(record.getOperationTime().toMSecsSinceEpoch());
    m_query.addBindValue(record.getStatus());
    m_query.addBindValue(record.getOutQuantity());
    if(!m_query.exec())
    {
        throw std::runtime_error("{异常}[DB]插入出库记录语句执行失败" + m_query.lastError().text().toStdString());
    }

    return m_query.lastInsertId().toLongLong();
}
/*=======================================<辅助函数>=======================================*/
QSqlQuery DatabaseManager::getQueryForAll(const QString &tableName)
{
    if(!m_DB.isOpen())
        throw std::runtime_error("{异常}[DB]查询指定表所有记录之前数据库没有被正确的打开，这一般是代码的问题");

    QSqlQuery q(m_DB);
    QString queryString = QString("SELECT * FROM %1").arg(tableName);
    q.prepare(queryString); // 使用 prepare 确保安全

    if (!q.exec())
    {
        throw std::runtime_error("{异常}[DB]执行查询" + tableName.toStdString() + "表所有记录语句失败: " + q.lastError().text().toStdString());
    }
    return q;
}

const QSqlDatabase& DatabaseManager::getDB()
{
    return m_DB;
}
